package com.lagou.module01.dao.imp;

import com.lagou.module01.bean.ClassGrade;
import com.lagou.module01.bean.PageBean;
import com.lagou.module01.dao.ClassGradeDao;
import com.lagou.module01.utils.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: xyk
 * @Date: Create in 21:37 2021/3/28
 * @Description:
 */
public class ClassGradeDaoImp implements ClassGradeDao {
    @Override
    public List<ClassGrade> classGradesShow(PageBean pageBean) {
        List<ClassGrade> classGradeList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select * from classgrade limit ?,?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, pageBean.getStart());
            preparedStatement.setInt(2, pageBean.getRows());
            resultSet = preparedStatement.executeQuery();
            // 4.将查询得到的学员信息存储到list中
            while (resultSet.next()) {
                String classId = resultSet.getString("class_id");
                String className = resultSet.getString("class_name");
                String grade = resultSet.getString("grade");
                String teacherName = String.valueOf(resultSet.getString("teacher_name"));
                String slogan = resultSet.getString("slogan");
                int studentNumber = resultSet.getInt("student_number");
                ClassGrade classGrade = new ClassGrade(classId, className, grade, teacherName, slogan, studentNumber);
                classGradeList.add(classGrade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, preparedStatement);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return classGradeList;
    }

    @Override
    public int getClassGradeCount() {
        int classGradeCount = 0;
        Connection connection = null;
        Statement stat = null;
        ResultSet resultSet = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select count(*) from classgrade";
            // 3.执行sql语句后获取结果并返回
            stat = connection.createStatement();;
            resultSet = stat.executeQuery(sql);
            // 4.将查询得到的学员总数
            resultSet.next();
            classGradeCount = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, stat);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return classGradeCount;
    }

    @Override
    public boolean insertClassGrade(ClassGrade classGrade) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "insert into classgrade(class_id, class_name, grade, teacher_name, slogan, student_number) values(?, ?, ?, ?, ?, ?)";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, classGrade.getClassId());
            preparedStatement.setString(2, classGrade.getClassName());
            preparedStatement.setString(3, classGrade.getGrade());
            preparedStatement.setString(4, classGrade.getTeacherName());
            preparedStatement.setString(5, classGrade.getSlogan());
            preparedStatement.setInt(6, classGrade.getStudentNumber());

            resultInt = preparedStatement.executeUpdate();
            // 4.将查询得到的学员信息存储到list中
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }

    @Override
    public boolean editClassGrade(ClassGrade classGrade) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "update classgrade set class_name=?, grade=?, teacher_name=?, slogan=?, student_number=? where class_id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, classGrade.getClassName());
            preparedStatement.setString(2, classGrade.getGrade());
            preparedStatement.setString(3, classGrade.getTeacherName());
            preparedStatement.setString(4, classGrade.getSlogan());
            preparedStatement.setInt(5, classGrade.getStudentNumber());
            preparedStatement.setString(6, classGrade.getClassId());

            resultInt = preparedStatement.executeUpdate();
            // 4.将查询得到的学员信息存储到list中
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }

    @Override
    public ClassGrade queryClassGrade(String classId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ClassGrade classGrade = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select * from classgrade where class_id = ? ";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, classId);
            resultSet = preparedStatement.executeQuery();

            // 4.获取查询得到的学员
            if (resultSet.next()) {
                String className = resultSet.getString("class_name");
                String grade = resultSet.getString("grade");
                String teacherName = resultSet.getString("teacher_name");
                String slogan = resultSet.getString("slogan");
                int studentNumber = resultSet.getInt("student_number");
                classGrade = new ClassGrade(classId, className, grade, teacherName, slogan, studentNumber);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, preparedStatement);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return classGrade;
    }

    @Override
    public boolean deleteClassGrade(String classId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "delete from classgrade where class_id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, classId);
            resultInt = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }
}
